﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

using MySql.Data.MySqlClient;

namespace JA.Negocios
{
    public static partial class ModelBO
    {

        public static int getImovelXCaracteristicasCount(int? idImovel, int? idCaracteristica)
        {
            string where = "WHERE ";
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT count(IdCaracteristica) qtd ");
            query.AppendLine("FROM imovelxcaracteristica ");

            if (idImovel != null)
            {
                query.AppendLine(where + "IdImovel=?IdImovel ");
                where = "AND ";
            }
            if (idCaracteristica != null)
            {
                query.AppendLine(where + "IdCaracteristica=?IdCaracteristica ");
                where = "AND ";
            }

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (idImovel != null) cmd.Parameters.AddWithValue("?IdImovel", idImovel);
            if (idCaracteristica != null) cmd.Parameters.AddWithValue("?IdCaracteristica", idCaracteristica);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                int quantidade = 0;

                if (reader.Read())
                {
                    quantidade = reader.GetInt32(reader.GetOrdinal("qtd"));
                }
                reader.Close();

                return quantidade;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }
        }
        public static void getImovelXCaracteristicas(ImovelVO imovelVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT ixc.IdCaracteristica, c.Caracteristica ");
            query.AppendLine("FROM imovelxcaracteristica ixc");
            query.AppendLine("     LEFT JOIN caracteristica c ON ixc.IdCaracteristica = c.IdCaracteristica ");
            query.AppendLine("WHERE ixc.IdImovel = ?IdImovel ");
            query.AppendLine("ORDER BY ixc.IdCaracteristica ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdImovel", imovelVO.idImovel);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                imovelVO.caracteristicaVOs.Clear();

                while (reader.Read())
                {
                    CaracteristicaVO caracteristicaVO = CaracteristicaVO.CreateCaracteristicaVO();
                    caracteristicaVO.idCaracteristica = reader.GetInt32(reader.GetOrdinal("IdCaracteristica"));
                    caracteristicaVO.caracteristica = reader.GetString(reader.GetOrdinal("Caracteristica"));
                    imovelVO.caracteristicaVOs.Add(caracteristicaVO);
                }
                reader.Close();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }
        }

        public static void newImovelxCaracteristicas(List<CaracteristicaVO> caracteristicaVOs, int idImovel)
        {
            foreach (CaracteristicaVO caracteristicaVO in caracteristicaVOs)
            {
                newImovelxCaracteristica(caracteristicaVO, idImovel);
            }
        }
        public static void newImovelxCaracteristica(CaracteristicaVO caracteristicaVO, int idImovel)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO imovelxcaracteristica ");
            query.AppendLine(" (  IdCaracteristica, IdImovel ) ");
            query.AppendLine("VALUES ( ?IdCaracteristica, ?IdImovel ) ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();
            cmd.Parameters.AddWithValue("?IdCaracteristica", caracteristicaVO.idCaracteristica);
            cmd.Parameters.AddWithValue("?IdImovel", idImovel);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static void delImovelxCaracteristicas(List<CaracteristicaVO> caracteristicaVOs, int idImovel)
        {
            foreach (CaracteristicaVO caracteristicaVO in caracteristicaVOs)
            {
                delImovelxCaracteristica(caracteristicaVO, idImovel);
            }
        }
        public static void delImovelxCaracteristica(CaracteristicaVO caracteristicaVO, int idImovel)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM imovelxcaracteristica ");
            query.AppendLine("WHERE IdCaracteristica=?IdCaracteristica and IdImovel=?IdImovel ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdCaracteristica", caracteristicaVO.idCaracteristica);
            cmd.Parameters.AddWithValue("?IdImovel", idImovel);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }
    }
}
